import cx_Oracle
import pandas as pd

cx_Oracle.init_oracle_client(lib_dir=r"C:\\tools\\instantclient_21_3")
conn = cx_Oracle.connect('DCFY/pan1231@59.110.242.52:50777/dcfydev')
curs=conn.cursor()
# 查询某个表数据
def query(table):
    # SQL语句，可以定制，实现灵活查询
    sql = 'select * from ' + table
    curs.execute(sql)
    results=curs.fetchone()
    # 使用pandas 的read_sql函数，可以直接将数据存放在dataframe中
    return results


def query_col(table_name):
    sql = f'''
    select t1.TABLE_NAME,t1.COLUMN_NAME,t1.COLUMN_ID,t2.COMMENTS from USER_TAB_COLS  t1
     left join USER_COL_COMMENTS  t2 on t1.COLUMN_NAME=t2.COLUMN_NAME and t1.TABLE_NAME=t2.TABLE_NAME
     where t1.TABLE_NAME= '{table_name}' order by t1.COLUMN_ID  
    '''
    res = pd.read_sql(sql, conn)
    res.append('cdd')
    return res
    pass


test_data = query("YS_YSSJ")  # 可以得到结果集
print(test_data)
# print(query_col("YS_YSSJ"))
# ext 生成
col_pattern = '%s String comment %s'
drop_pattern = '''drop table fdc_ext.%s;'''

creat_pattern = '''
drop table fdc_ext.%s ; 
CREATE  TABLE fdc_ext.%s(
 %s
)
COMMENT '%s'
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.tdt.JDBCSerde'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.tdt.JDBCDBInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
TBLPROPERTIES (
 'mapreduce.jdbc.input.query'='%s',
 'mapreduce.jdbc.password'='pan1231',
 'mapreduce.jdbc.input.count.query'='select count(*) from %s ',
 'mapreduce.jdbc.driver.class'='oracle.jdbc.driver.OracleDriver',
 'mapreduce.jdbc.driver.file'='/tmp/transporter1/drivers/jars/ojdbc7-12.1.0.2.jar',
 'mapreduce.jdbc.username'='DCFY',
 'mapreduce.jdbc.url'='jdbc:oracle:thin:@59.110.242.52:50777/dcfydev')
;
'''

# ods 生成
